
CREATE TABLE AdForSale
(
	AdId int CONSTRAINT PK_tblAdForSale	PRIMARY KEY IDENTITY(1,1),
	TransmissionTypeId int,
	ModelId int NOT NULL,
	ColorId int,
	YearMade int,
	Price int,
	MileAge int,
	Description nvarchar(MAX),
	TimePosted smalldatetime,
	ContactName nvarchar(100),
	ContactPhone nvarchar(100),
	ContactEmail nvarchar(100),
	EditKey nvarchar(30),
	StatusId int,
	CityId int,
	VehicleConditionId int,
	LastEditTime smalldatetime,
	ListingTypeId int,
	PostedByUserAccountId int,
	NumClickedOn int default 0,
	NumSaved int default 0
)

CREATE TABLE ForTransferLease
(
	AdId int CONSTRAINT PK_tblAdForTransferLease	PRIMARY KEY IDENTITY(1,1)
)

CREATE TABLE VehicleType
(
	VehicleTypeId int CONSTRAINT PK_tblVehicleType	PRIMARY KEY IDENTITY(1,1),
	VehicleTypeName nvarchar(50) NOT NULL CONSTRAINT UN_tblVehicleType_VehicleTypeName UNIQUE
)

CREATE TABLE Make
(
	MakeId int CONSTRAINT PK_tblMake	PRIMARY KEY IDENTITY(1,1),
	MakeName nvarchar(80) NOT NULL CONSTRAINT UN_tblMake_MakeName UNIQUE
)

CREATE TABLE Model
(
	ModelId int CONSTRAINT PK_tblModel	PRIMARY KEY IDENTITY(1,1),
	MakeId int NOT NULL,
	ModelName nvarchar(100) 
)

CREATE TABLE ForSaleCommonTag
(
	TagId int CONSTRAINT PK_tblForSaleCommonTag	PRIMARY KEY IDENTITY(1,1),
	TagName nvarchar(100) 
)

CREATE TABLE AdStatus
(
	StatusId int CONSTRAINT PK_tblAdStatus	PRIMARY KEY IDENTITY(1,1),
	StatusName nvarchar(100)
)

CREATE TABLE City
(
	CityId int CONSTRAINT PK_tblCity PRIMARY KEY IDENTITY(1,1),
	CityName nvarchar(50) NOT NULL CONSTRAINT UN_tblCity_CityName UNIQUE,
	ProvinceId int
)

CREATE TABLE Province
(
	ProvinceId int CONSTRAINT PK_tblProvince PRIMARY KEY IDENTITY(1,1),
	ProvinceName nvarchar(50) NOT NULL CONSTRAINT UN_tblProvince_ProvinceName UNIQUE
)

CREATE TABLE Color
(
	ColorId int CONSTRAINT PK_tblColor PRIMARY KEY IDENTITY(1,1),
	ColorName nvarchar(50) NOT NULL CONSTRAINT UN_tblColor_ColorName UNIQUE
)

CREATE TABLE Photo
(
	PhotoId int CONSTRAINT PK_tblPhoto PRIMARY KEY IDENTITY(1,1),
	OriginalName nvarchar(100),
	PathOnServer nvarchar(400) NOT NULL CONSTRAINT UN_tblPhoto_PathOnServer UNIQUE,
	PhotoSize int,
	UploadDateTime smalldatetime,
	AdTypeId int,
	AdId int,
	IsProfilePhoto bit,
	DisplayPosition int
)

CREATE TABLE TransmissionType
(
	TransmissionTypeId int CONSTRAINT PK_tblTransmissionType PRIMARY KEY IDENTITY(1,1),
	TransmissionTypeName nvarchar(30) NOT NULL CONSTRAINT UN_tblTransmissionType_TransmissionTypeName UNIQUE
)

CREATE TABLE VehicleCondition
(
	VehicleConditionId int CONSTRAINT PK_tblVehicleCondition PRIMARY KEY IDENTITY(1,1),
	VehicleConditionName nvarchar(30) NOT NULL CONSTRAINT UN_tblVehicleCondition_VehicleConditionName UNIQUE
)

CREATE TABLE OrderByOption
(
	OrderByOptionId int CONSTRAINT PK_tblOrderByOption PRIMARY KEY IDENTITY(1,1),
	OrderByOptionCode int,
	CultureCode nvarchar(10) NOT NULL,
	OrderByOptionName nvarchar(30) NOT NULL UNIQUE
)

CREATE TABLE ListingType
(
	ListingTypeId int CONSTRAINT PK_tblListingType PRIMARY KEY IDENTITY(1,1),
	ListingTypeName nvarchar(30) NOT NULL UNIQUE
)

CREATE TABLE WebMessage
(
	MessageCode nvarchar(30) NOT NULL,
	CultureId int,
	MessageText nvarchar(max) NOT NULL
	PRIMARY KEY (MessageCode, CultureId)
)

CREATE TABLE UserAccount
(
	UserAccountId int CONSTRAINT PK_tblUserAccount PRIMARY KEY IDENTITY(1,1),
	Email nvarchar(50) NOT NULL	CONSTRAINT UN_tblUserAccount_Email UNIQUE,
	Username nvarchar(50) NOT NULL CONSTRAINT UN_tblUserAccount_Username UNIQUE,
	Password nvarchar(50) NOT NULL,
	ContactName nvarchar(100),
	ContactPhone nvarchar(50),
	AlternativePhone nvarchar(50),
	AccountStateId int NOT NULL,
	ActivationCode uniqueidentifier,
	ActivationEmailSentDateTime smalldatetime,
	DateActivated smalldatetime,
	ChangePasswordEmailSentDateTime smalldatetime,
	LastPasswordChangedDateTime smalldatetime,
	TempPassword nvarchar(50),
	TempPasswordEmailSentDateTime smalldatetime,
	LastLoginDate smalldatetime,
	UserLogoFilePath nvarchar(150),
	UserProfilePhotoPath nvarchar(150),
	Intro nvarchar(MAX)
)

CREATE TABLE UserSavedPost
(
	UserAccountId int NOT NULL,
	PostId int NOT NULL
	Primary key (UserAccountId, PostId)
)

CREATE TABLE SecurityMessage
(
	MessageCode nvarchar(100) NOT NULL,
	CultureId int,
	MessageText nvarchar(max) NOT NULL
	PRIMARY KEY (MessageCode, CultureId)
)

